package com.example.ntier.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.example.ntier.model.entity.Department;

public class DepartmentDAL extends AbstractDAL<Integer, Department> {

	public DepartmentDAL() {
		super("DEPARTMENTS");
	}

	@Override
	public boolean insert(Department entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "INSERT INTO departments VALUES (DEP_SEQ.nextval, ?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd, new String[] { "DEPARTMENT_NUMBER" })) {
				statement.setString(1, entity.getName());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
						if (generatedKeys.next()) {
							entity.setId(generatedKeys.getInt(1));
						}
					}
				}
			}
		}
		return result;
	}

	@Override
	public boolean update(Department entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "UPDATE departments SET department_name = ? WHERE department_number = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setString(1, entity.getName());
				statement.setInt(2, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean delete(Department entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "DELETE FROM departments WHERE department_number = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public Department findById(Integer id) throws SQLException {
		Department department = null;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "SELECT * FROM departments WHERE department_number = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, id);
				try (ResultSet resultSet = statement.executeQuery();) {
					if (resultSet.next()) {
						department = createEntity(resultSet);
					}
				}
			}
		}
		return department;
	}

	@Override
	public List<Department> getAll() throws SQLException {
		List<Department> departments = new ArrayList<Department>();
		try (Connection connection = createConnection()) {
			try (Statement statement = connection.createStatement()) {
				final String sqlCmd = "SELECT * FROM departments";
				try (ResultSet resultSet = statement.executeQuery(sqlCmd)) {
					while (resultSet.next()) {
						departments.add(createEntity(resultSet));
					}
				}
			}
		}
		return departments;
	}

	@Override
	Department createEntity(ResultSet resultSet) throws SQLException {
		Department department = new Department();
		department.setId(resultSet.getInt("department_number"));
		department.setName(resultSet.getString("department_name"));
		return department;
	}

}
